using Homework8.Models;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;

namespace Homework8.Services
{
    public class SchoolService
    {
        public List<School> GetAllSchools()
        {
            List<School> schools = new List<School>();
            
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "SELECT * FROM School";
            using var command = new MySqlCommand(sql, connection);
            using var reader = command.ExecuteReader();
            
            while (reader.Read())
            {
                schools.Add(new School
                {
                    SchoolId = reader.GetInt32("SchoolId"),
                    Name = reader.GetString("Name"),
                    Address = reader.IsDBNull(reader.GetOrdinal("Address")) ? null : reader.GetString("Address"),
                    FoundingYear = reader.IsDBNull(reader.GetOrdinal("FoundingYear")) ? null : reader.GetInt32("FoundingYear")
                });
            }
            
            return schools;
        }
        
        public School? GetSchoolById(int schoolId)
        {
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "SELECT * FROM School WHERE SchoolId = @SchoolId";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@SchoolId", schoolId);
            
            using var reader = command.ExecuteReader();
            
            if (reader.Read())
            {
                return new School
                {
                    SchoolId = reader.GetInt32("SchoolId"),
                    Name = reader.GetString("Name"),
                    Address = reader.IsDBNull(reader.GetOrdinal("Address")) ? null : reader.GetString("Address"),
                    FoundingYear = reader.IsDBNull(reader.GetOrdinal("FoundingYear")) ? null : reader.GetInt32("FoundingYear")
                };
            }
            
            return null;
        }
        
        public void AddSchool(School school)
        {
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "INSERT INTO School (Name, Address, FoundingYear) VALUES (@Name, @Address, @FoundingYear); SELECT LAST_INSERT_ID();";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@Name", school.Name);
            command.Parameters.AddWithValue("@Address", school.Address ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@FoundingYear", school.FoundingYear ?? (object)DBNull.Value);
            
            var schoolId = Convert.ToInt32(command.ExecuteScalar());
            school.SchoolId = schoolId;
            
            // 记录操作日志
            DBHelper.LogOperation("添加", "School", schoolId, $"添加学校：{school.Name}");
        }
        
        public void UpdateSchool(School school)
        {
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "UPDATE School SET Name = @Name, Address = @Address, FoundingYear = @FoundingYear WHERE SchoolId = @SchoolId";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@SchoolId", school.SchoolId);
            command.Parameters.AddWithValue("@Name", school.Name);
            command.Parameters.AddWithValue("@Address", school.Address ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@FoundingYear", school.FoundingYear ?? (object)DBNull.Value);
            
            command.ExecuteNonQuery();
            
            // 记录操作日志
            DBHelper.LogOperation("修改", "School", school.SchoolId, $"修改学校：{school.Name}");
        }
        
        public bool DeleteSchool(int schoolId)
        {
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            // 检查是否有关联的班级
            string checkSql = "SELECT COUNT(*) FROM Class WHERE SchoolId = @SchoolId";
            using (var checkCommand = new MySqlCommand(checkSql, connection))
            {
                checkCommand.Parameters.AddWithValue("@SchoolId", schoolId);
                var count = Convert.ToInt32(checkCommand.ExecuteScalar());
                if (count > 0)
                {
                    return false; // 有关联班级，不能删除
                }
            }
            
            // 获取学校名称用于日志
            string schoolName = "";
            using (var nameCommand = new MySqlCommand("SELECT Name FROM School WHERE SchoolId = @SchoolId", connection))
            {
                nameCommand.Parameters.AddWithValue("@SchoolId", schoolId);
                var nameResult = nameCommand.ExecuteScalar();
                schoolName = nameResult != null ? nameResult.ToString() ?? "" : "";
            }
            
            // 执行删除
            string sql = "DELETE FROM School WHERE SchoolId = @SchoolId";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@SchoolId", schoolId);
            
            int result = command.ExecuteNonQuery();
            
            if (result > 0)
            {
                // 记录操作日志
                DBHelper.LogOperation("删除", "School", schoolId, $"删除学校：{schoolName}");
                return true;
            }
            
            return false;
        }
    }
} 